![]() |
![]() |
![]() |
![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() |
![]() |
![]() |
To access the contents, click the chapter and section titles.
Oracle Performance Tuning and Optimization
Using LocksLocks are usually handled transparently for the user and are created and released automatically when the transaction has been committed or rolled back. Even with this transparency, it is important for you to realize that the locks are held until the commit or rollback. Long-running ad-hoc transactions that are not committed can cause contention problems on the system if they are not addressed.
Explicit Locking Tables can be explicitly locked by using the LOCK TABLE command. With this command, you can manually lock and unlock tables in different modes. By manually locking tables, you can have a lot of flexibility over how the locks are donebut there is a danger. Locking tables manually can result in locks that are held longer than necessary and a loss of flexibility for the application. Manual locking should be reserved for the most-experienced programmer and only under extreme conditions. I dont recommend using manual locking at all. The SELECT...FOR UPDATE Statement The SELECT...FOR UPDATE statement allows you to explicitly lock a set of rows that you will be updating. This statement allows you to update several rows as a group and be assured that none of the rows can change until you have finished your transaction. In this manner, no other process can acquire a lock on one of the elements in your transaction until you finish with all of them. Using the SELECT...FOR UPDATE statement is much more efficient than locking the table explicitly. Deadlocks Deadlocks occur when two (or more) processes hold resources that the other one needs. Because neither of the processes will release its resource until it has received the others resource, neither can proceed. If a deadlock occurs, Oracle breaks the deadlock by forcing one or more of the processes to roll back and release the resources. By carefully designing your application to acquire locks in sequences, you can avoid deadlocks.
Array ProcessingWhen inserting a large number of rows into a table, you may be able to improve performance by taking advantage of array processing. Array processing allows multiple values to be passed to Oracle in one statement. Array processing reduces the number of calls to Oracle and reduces network overhead because a large network packet is much more efficient to send than many small packets. Consider the following embedded SQL statement: INSERT INTO dogs ( dogname, age, breed, owner ) VALUES ( :d_name, :d_age, :d_breed, :d_owner ); If the variables bound to d_name, d_age, d_breed, and d_owner are simple variables, then Oracle executes one statement. If the variables bound to those names are arrays, the entire array is sent to Oracle for processing. The SQL loader takes advantage of array processing to load rows in the most efficient manner possible; so do the Import and Export utilities. Using VARCHAR2 instead of CHARBy using the VARCHAR2 data type instead of the CHAR data type, you can eliminate unnecessary blank padding. The VARCHAR2 variable type stores a variable-length string; CHAR has a fixed-length format. By using VARCHAR2 instead of CHAR, you save space in the database as well as network and CPU resources. Although the VARCHAR data type is also supported, its use is not recommended. SummaryThis chapter is intended to wrap up all the miscellaneous tips and suggestions concerning the tuning of SQL statements. The chapter was a collection of unrelated items that didnt quite fit in any of the other chapters in Part IV of this book. Having finished this chapter, you should be familiar with the following topics:
Having finished Part IV of this book, Tuning SQL, you should have a solid understanding of the importance of tuning your SQL statements and a better idea of how to do that.
|
![]() |
Products | Contact Us | About Us | Privacy | Ad Info | Home
Use of this site is subject to certain Terms & Conditions, Copyright © 1996-2000 EarthWeb Inc. All rights reserved. Reproduction whole or in part in any form or medium without express written permission of EarthWeb is prohibited. |